const express = require('express');
const router = express.Router();
const db = require('../db'); // 引入数据库连接

// 学生表默认密码设置为学号
router.post('/students', (req, res) => {
    const { name, studentId, class_name, dorm_id, building_id, floor_id, contact } = req.body;
    const password = studentId; // 默认密码为学号

    const query = `INSERT INTO students (name, studentId, class_name, dorm_id, building_id, floor_id, contact, password)
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)`;
    db.query(query, [name, studentId, class_name, dorm_id, building_id, floor_id, contact, password], (err, results) => {
        if (err) {
            console.error(err);
            res.status(500).send('添加学生失败');
        } else {
            res.send('学生添加成功');
        }
    });
});

// 学生查询（带分页和筛选功能）
router.get('/students', (req, res) => {
    const { page = 1, size = 10, search = '', class_name, dorm_id } = req.query;
    const offset = (page - 1) * size;
    let baseQuery = 'SELECT * FROM students WHERE 1=1';
    const params = [];

    if (search) {
        baseQuery += ' AND (name LIKE ? OR studentId LIKE ?)';
        params.push(`%${search}%`, `%${search}%`);
    }

    if (class_name) {
        baseQuery += ' AND class_name = ?';
        params.push(class_name);
    }

    if (dorm_id) {
        baseQuery += ' AND dorm_id = ?';
        params.push(dorm_id);
    }

    const paginatedQuery = `${baseQuery} LIMIT ?, ?`;
    params.push(Number(offset), Number(size));

    db.query(paginatedQuery, params, (err, results) => {
        if (err) {
            console.error(err);
            res.status(500).send('查询学生失败');
        } else {
            db.query('SELECT COUNT(*) AS total FROM students WHERE 1=1', (err, countResults) => {
                if (err) {
                    console.error(err);
                    res.status(500).send('统计总数失败');
                } else {
                    res.send({
                        total: countResults[0].total,
                        data: results,
                    });
                }
            });
        }
    });
});


// 编辑学生
router.put('/students/:id', (req, res) => {
    const { id } = req.params;
    const { name, studentId, class_name, dorm_id, building_id, floor_id, contact, password } = req.body;
    const sql = 'UPDATE students SET name = ?, studentId = ?, class_name = ?, dorm_id = ?, building_id = ?, floor_id = ?, contact = ?, password = ? WHERE id = ?';
    const params = [name, studentId, class_name, dorm_id, building_id, floor_id, contact, password, id];
    db.query(sql, params, (err, result) => {
        if (err) return res.status(500).send(err.message);
        res.send({ message: '学生信息更新成功' });
    });
});

// 删除学生
router.delete('/students/:id', (req, res) => {
    const { id } = req.params;
    const sql = 'DELETE FROM students WHERE id = ?';
    db.query(sql, id, (err, result) => {
        if (err) return res.status(500).send(err.message);
        res.send({ message: '学生删除成功' });
    });
});

module.exports = router;